﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.IO;
using MySql.Data.MySqlClient;

namespace kwcode
{
    public partial class Kwcode : Form
    {
        public Kwcode()
        {
            InitializeComponent();
            LoadInit();
        }

        /// <summary>
        /// 获取配置信息
        /// </summary>
        private void LoadInit()
        {
            XElement config = XElement.Load(System.Windows.Forms.Application.StartupPath + "/config.xml");
            string connString = config.Element("Node").Attribute("connString").Value;
            this.txtConnString.Text = connString;
            this.cboType.SelectedIndex = 0;
        }

        public List<string> tNames = new List<string>();  //所有表名
        private bool isDeletePrefix = false;        //默认不选择删除表前缀

        /// <summary>
        /// 获取表信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnGetTables_Click(object sender, EventArgs e)
        {
            this.lblMessage.Text = "提示：请选择要生成代码对应的表。";
            tNames = new List<string>();
            if (this.txtConnString.Text.Trim() == "")
            {
                MessageBox.Show("连接字符串不能为空！");
                return;
            }
            if (this.cboType.SelectedIndex == 0)
            {
                string connString = this.txtConnString.Text.Trim();
                SqlConnection connection = new SqlConnection(connString);
                try
                {
                    this.clbTables.Items.Clear();
                    string sql = "SELECT name FROM sys.tables UNION ALL SELECT name FROM sys.views ORDER BY name";
                    SqlCommand command = new SqlCommand(sql, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        clbTables.Items.Add(reader["name"].ToString());
                        tNames.Add(reader["name"].ToString());
                    }
                    this.lblMessage.Text = "提示：成功获取表数据！";
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
            }
            else if (this.cboType.SelectedIndex == 1)
            {
                string connString = this.txtConnString.Text.Trim();
                MySqlConnection connection = new MySqlConnection(connString);
                try
                {
                    this.clbTables.Items.Clear();
                    string sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
                    MySqlCommand command = new MySqlCommand(sql, connection);
                    connection.Open();
                    MySqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        clbTables.Items.Add(reader["TABLE_NAME"].ToString());
                        tNames.Add(reader["TABLE_NAME"].ToString());
                    }
                    this.lblMessage.Text = "提示：成功获取表数据！";
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    connection.Close();
                }
            }
            ((Button)sender).BackColor = System.Drawing.Color.White;
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnTestConnect_Click_1(object sender, EventArgs e)
        {
            if (this.txtConnString.Text.Trim() == "")
            {
                this.lblMessage.Text = "提示：连接字符串不能为空！";
                return;
            }
            if (this.cboType.SelectedIndex == 0) {
                string connString = this.txtConnString.Text.Trim();
                SqlConnection connection = new SqlConnection(connString);
                try {
                    connection.Open();
                    this.lblMessage.Text = "提示：连接数据库成功！";
                }
                catch (Exception ex) {
                    this.lblMessage.Text = "提示：连接数据库失败！";
                    MessageBox.Show(ex.Message);
                }
                finally {
                    connection.Close();
                }
            }else if (this.cboType.SelectedIndex == 1) {
                string connString = this.txtConnString.Text.Trim();
                MySqlConnection connection = new MySqlConnection(connString);
                try {
                    connection.Open();
                    this.lblMessage.Text = "提示：连接数据库成功！";
                }
                catch (Exception ex) {
                    this.lblMessage.Text = "提示：连接数据库失败！";
                    MessageBox.Show(ex.Message);
                }
                finally {
                    connection.Close();
                }
            }
            ((Button)sender).BackColor = System.Drawing.Color.White;
        }

        /// <summary>
        /// 生成文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnCreateFiles_Click(object sender, EventArgs e)
        {
            if (isDeletePrefix && this.txtPrefix.Text.Trim() == "")
            {
                MessageBox.Show("如果需要去除表前缀，请填写表的前缀。");
                return;
            }
            this.tspbStatus.Value = 0;

            //循环要处理的表
            foreach (var item in clbTables.CheckedItems)
            {
                string OriginalTableName= item.ToString().Trim();
                string TableName = item.ToString().Trim();
                //去除前缀
                if (isDeletePrefix) TableName = Regex.Replace(TableName,"^"+ this.txtPrefix.Text.Trim(), "");
                //处理下划线分隔的表名
                string[] tableNameArray = TableName.Split('_', '-');
                TableName = "";
                for (int i=0;i< tableNameArray.Length;i++) {
                    //首字母大写
                    tableNameArray[i] = tableNameArray[i].Substring(0, 1).ToUpper() + tableNameArray[i].Substring(1);
                    TableName += tableNameArray[i];
                }
                string table_name = string.Join("-", tableNameArray).ToLower();
                string tableName = TableName.Substring(0, 1).ToLower() + TableName.Substring(1);
                string TableNames = TableName + "s";
                string tableNames = tableName + "s";
                string CNName = TableName;   //中文名称
                string TableShortName = TableName;
                if (TableName.Length==4 && (TableName.Substring(0,4)=="Data" || TableName.Substring(0, 4) == "Base")) {
                    TableShortName = TableName.Substring(4);
                }else if (TableName.Length == 4 && (TableName.Substring(0, 8) == "Schedule")) {
                    TableShortName = TableName.Substring(8);
                }

                //处理名称的复数形式
                if (TableName.EndsWith("y"))
                {
                    TableNames = TableName.Substring(0, TableName.Length - 1) + "ies";
                    tableNames = tableName.Substring(0, tableName.Length - 1) + "ies";
                }
                if (TableName.EndsWith("ss"))
                {
                    TableNames = TableName + "es";
                    tableNames = tableName + "es";
                }
                if (TableName.EndsWith("s"))
                {
                    TableNames = TableName;
                    tableNames = tableName;
                }

                List<TableColumn> columns = GetTableColumns(OriginalTableName); //所有字段
                ProcessFile(TableName, tableName, TableNames, tableNames,table_name, CNName, TableShortName, columns); //处理文件
            }
            this.lblMessage.Text = "提示：文件生成完成";
            this.tspbStatus.Value = 100;
            ((Button)sender).BackColor = System.Drawing.Color.White;
        }

        /// <summary>
        /// 处理文件
        /// </summary>
        /// <returns></returns>
        private void ProcessFile(string TableName, string tableName, string TableNames, string tableNames,string table_name, string CNName,string TableShortName, List<TableColumn> columns)
        {
            //创建目录
            if (!Directory.Exists("generator-code"))
            {
                Directory.CreateDirectory("generator-code");
            }
            if (!Directory.Exists("template")) 
            {
                Directory.CreateDirectory("template");
            }
            
            //循环目录中所有文件，替换内容并保存
            foreach (string file in Directory.GetFiles("template", "*", SearchOption.AllDirectories))
            {
                List<TableColumn> tempColumns = columns;
                //文件名
                string saveFileName = file
                    .Replace("template", "generator-code")
                    .Replace("$TableName", TableName)
                    .Replace("$tableName", tableName)
                    .Replace("$TableShortName",TableShortName);
                string content = KwHelper.Read(file);  //读取文件
                //替换表名（复数、大小写）
                content = content
                    .Replace("$TableNames", TableNames)
                    .Replace("$tableNames", tableNames)
                    .Replace("$table-name",table_name)
                    .Replace("$TableShortName",TableShortName);
                content = Regex.Replace(content, @"\$TableName(?=[^s])", TableName);
                content = Regex.Replace(content, @"\$tableName(?=[^s])", tableName);
                //中文名称替换
                foreach (Control control in this.panCNName.Controls)
                {
                    if (control is TextBox && control.Name.Replace("_","").ToLower() == tableName.ToLower())
                    {
                        CNName = control.Text;
                    }
                }
                content = content.Replace("$CNName", CNName);

                #region  处理表字段循环
                MatchCollection matches = Regex.Matches(content, @"(?<=\$foreach\s*\${).*?(?=\$})", RegexOptions.IgnoreCase | RegexOptions.Singleline);  //匹配的集合
                foreach (Match match in matches)
                {
                    string value = match.Value;
                    //不匹配的字段
                    string notMap = Regex.Match(value, @"(?<=\[NotMap\().*?(?=\)\])", RegexOptions.IgnoreCase | RegexOptions.Singleline).Value;
                    string[] notMapColumn = notMap.Split(',');  //不匹配字段数组
                    //要替换字段的内容
                    string mapContent = Regex.Replace(value, @"\s*\[NotMap\(.*?\)\]\s*", "", RegexOptions.IgnoreCase | RegexOptions.Singleline);
                    string subContent = "";
                    foreach (TableColumn column in tempColumns)
                    {
                        if (notMapColumn.Where(x => x.ToLower() == column.Name.ToLower()).Count() > 0) continue;  //跳过不匹配的字段
                        string temp = mapContent.Replace("$TableColumnName", column.Name).Replace("$TableColumnCNName", column.CNName).Replace("$TableColumnType", column.DataType);
                        subContent = subContent + temp;
                    }
                    Regex rg = new Regex(@"\$foreach\s*\${.*?\$}", RegexOptions.IgnoreCase | RegexOptions.Singleline);
                    content=rg.Replace(content,subContent,1).Trim();
                    if (content.EndsWith(@"\r\n"))
                    {
                        content = content.Substring(0, content.LastIndexOf(@"\r\n"));
                    }
                }
                #endregion

                //处理表
                MatchCollection table = Regex.Matches(content, @"(?<=\$foreach\(table\)\s*\${).*?(?=\$})", RegexOptions.IgnoreCase | RegexOptions.Singleline);
                foreach (Match match in table)
                {
                    string value = match.Value;
                    string subContent = "";
                    foreach (string name in tNames)  //循环所有要处理表名
                    {
                        string temp = value.Replace("$TableMapName", name);
                        subContent = subContent + temp;
                    }
                    Regex rg = new Regex(@"\$foreach\(table\)\s*\${.*?\$}", RegexOptions.IgnoreCase | RegexOptions.Singleline);
                    content = rg.Replace(content, subContent, 1).Trim();
                }
                KwHelper.Write(saveFileName, content);
            }
        }

        /// <summary>
        /// 获取表中所有字段的集合
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private List<TableColumn> GetTableColumns(string tableName)
        {
            List<TableColumn> columns = new List<TableColumn>();
            string connString = this.txtConnString.Text.Trim();
            if (this.cboType.SelectedIndex == 0) {
                SqlConnection connection = new SqlConnection(connString);
                try {
                    //string sql = "SELECT column_name,is_nullAble,Data_type FROM information_schema.COLUMNS WHERE TABLE_NAME='" + tableName + "'"; //查询表中所有列
                    string sql =
                        "SELECT t.[name] AS 表名,column_name,d.is_nullAble,Data_type,cast(ep.[value] as nvarchar(200)) AS CNName "
                        +
                        " FROM sys.tables AS t  INNER JOIN sys.columns   AS c ON t.object_id = c.object_id  " +
                        "LEFT JOIN sys.extended_properties AS ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id "
                        +
                        "INNER JOIN information_schema.COLUMNS AS d ON c.[name]=d.column_name " +
                        "Where t.name=TABLE_NAME AND TABLE_NAME='" + tableName + "' ORDER BY c.column_id";
                    SqlCommand command = new SqlCommand(sql, connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read()) {
                        TableColumn column = new TableColumn();
                        bool isNull = reader["is_nullAble"].ToString().Trim().ToUpper() == "YES";
                        column.DataType = KwHelper
                            .SqlTypeToCsharpType(reader["Data_type"].ToString().Trim().ToLower(), isNull).ToString()
                            .Trim();
                        column.Name = reader["column_name"].ToString().Trim();
                        column.CNName = reader["CNName"].ToString().Trim();
                        if (string.IsNullOrEmpty(column.CNName))
                            column.CNName = column.Name;
                        columns.Add(column);
                    }
                }
                catch (Exception ex) {
                    MessageBox.Show(ex.ToString());
                }
                finally {
                    connection.Close();
                }
            }else if (this.cboType.SelectedIndex == 1) {
                MySqlConnection connection = new MySqlConnection(connString);
                try {
                    
                    string sql = "select * from INFORMATION_SCHEMA.Columns where table_name='" + tableName + "' ";

                    MySqlCommand command = new MySqlCommand(sql, connection);
                    connection.Open();
                    MySqlDataReader reader = command.ExecuteReader();
                    while (reader.Read()) {
                        TableColumn column = new TableColumn();
                        bool isNull = reader["is_nullAble"].ToString().Trim().ToUpper() == "YES";
                        column.DataType = KwHelper
                            .SqlTypeToCsharpType(reader["Data_type"].ToString().Trim().ToLower(), isNull).ToString()
                            .Trim();
                        column.Name = reader["column_name"].ToString().Trim();
                        column.CNName = reader["column_comment"].ToString().Trim();
                        if (string.IsNullOrEmpty(column.CNName))
                            column.CNName = column.Name;
                        columns.Add(column);
                    }
                }
                catch (Exception ex) {
                    MessageBox.Show(ex.ToString());
                }
                finally {
                    connection.Close();
                }
            }
            return columns;
        }

        /// <summary>
        /// 设置中文名称
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnCreateCNName_Click(object sender, EventArgs e)
        {
            this.lblMessage.Text = "提示：填写完表格对应中文名称后可以生成代码。";
            this.btnCreateFiles.Enabled = true;

            if (isDeletePrefix &&this.txtPrefix.Text.Trim() == "")
            {
                MessageBox.Show("如果需要去除表前缀，请填写表的前缀。");
                return;
            }
            
            //删除所有textbox控件
            this.panCNName.Controls.Clear();

            int x = 15;
            int y = -30;
            //循环选中的表
            for (int i = 0, j = 1; i < clbTables.CheckedItems.Count; i++, j++)
            {
                //设置控件坐标位置
                if (j <= 3 && j > 1)
                {
                    x = x + 160;
                }
                else
                {
                    y = y + 30;
                    x = 15;
                    j = 1;
                }
                TextBox box = new TextBox();
                box.Location = new System.Drawing.Point(x, y);
                if (isDeletePrefix)
                {
                    string data= Regex.Replace(clbTables.CheckedItems[i].ToString(), "^" + this.txtPrefix.Text.Trim(), "");
                    box.Name = data;
                    box.Text = data;
                }
                else
                {
                    box.Name = clbTables.CheckedItems[i].ToString();
                    box.Text = clbTables.CheckedItems[i].ToString();
                }
                box.Size = new System.Drawing.Size(150, 25);
                this.panCNName.Controls.Add(box);

            }
            ((Button)sender).BackColor = System.Drawing.Color.White;
        }


        //双击表格复选框
        private void clbTables_DoubleClick(object sender, EventArgs e)
        {
            this.btnCreateCNName.Enabled = true;
            this.lblMessage.Text = "提示：请设置表的中文名称。";
        }


        //打开源码网页
        private void tsbtnHelp_Click(object sender, EventArgs e)
        {
            //调用系统默认的浏览器 
            System.Diagnostics.Process.Start("https://gitee.com/kerwincui/kwcode");
        }

        private void chkPrefix_CheckedChanged(object sender, EventArgs e)
        {
            isDeletePrefix = this.chkPrefix.Checked;
        }
    }
}
